# -*- encoding: utf-8 -*-
##############################################################################
#
#    OpenERP, Open Source Management Solution
#    Copyright (C) 2004-2009 Tiny SPRL (<http://tiny.be>). All Rights Reserved
#    $Id$
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
##############################################################################

from osv import fields
from osv import osv
import wizard
import netsvc
import pooler
import time
from osv.orm import browse_record

class wizard_update_contract_status(wizard.interface):
    
    def _update_contract_staus(self, cr, uid, data, context):
        #Status is Completed, Oustanding is None
        cr.execute("Update kdvn_contract_client set status='completed',outstanding='none' \
                    where \
                        coalesce(status,'')<>'completed' and\
                        id in \
                            (Select \
                                kcc.id \
                            from \
                                kdvn_contract_client kcc \
                            where \
                                (attached_approved_quotation=True or attached_contract_received=True)\
                                and exists(Select id from kdvn_payment_from_client kpfc where kpfc.contract_id = kcc.id limit 1)\
                                and kcc.id not in (Select \
                                            contract_id \
                                        from \
                                            kdvn_payment_from_client\
                                        where coalesce(attached_progress_received,False)=False and coalesce(contract_id,0)>0)\
                                and kcc.id not in (Select \
                                            kcc.id \
                                        from \
                                            kdvn_contract_client kcc\
                                        left join\
                                            (Select\
                                                kpfc.contract_id,\
                                                sum(coalesce(kpvi.amount,0)) as total_vat_amount\
                                            from\
                                                kdvn_payment_from_client kpfc\
                                            left join\
                                                kdvn_payment_vat_invoice kpvi on kpfc.id = kpvi.payment_id\
                                            group by contract_id) vwpayment_vat on kcc.id = vwpayment_vat.contract_id\
                                        left join     \
                                            (Select \
                                                kcc.id,\
                                                ((coalesce(kcc.contract_amount_e,0)+coalesce(kcc.contract_amount_m,0))-coalesce(received_amount_as_contract_currency,0))*coalesce(fncompute_contract_currency_id(contract_currency),0)+coalesce(vwreceived.received_vnd,0) as contract_amount_vnd,\
                                                (coalesce(kcc.contract_vat,0)-coalesce(received_tax_amount_as_contract_currency,0))*coalesce(fncompute_contract_currency_id(contract_currency),0)+coalesce(vwreceived.received_tax_vnd,0) as contract_vat_vnd,\
                                                received_vnd,\
                                                received_tax_vnd\
                                            from \
                                                kdvn_contract_client kcc\
                                            left join     \
                                                (Select \
                                                    kpfc.contract_id,\
                                                    sum(coalesce(kr.amount,0)*fncompute_contract_currency_id(kpfc.contract_currency_id,pc.id)) as received_amount_as_contract_currency,\
                                                    sum(coalesce(tax_amount,0)*fncompute_contract_currency_id(kpfc.contract_currency_id,pc.id)) as received_tax_amount_as_contract_currency,\
                                                    sum(coalesce(actual_amount,0)) as received_vnd,\
                                                    sum(coalesce(actual_tax_amount,0)) as received_tax_vnd\
                                                from \
                                                    kdvn_payment_from_client kpfc\
                                                left join \
                                                    kdvn_received kr on kpfc.id=payment_id\
                                                left join \
                                                    project_currency pc on kpfc.contract_id = pc.contract_id and default_curr=True\
                                                where \
                                                    kpfc.state<>'draft' and kpfc.state<>'cancel'\
                                                group by\
                                                    kpfc.contract_id) vwreceived on kcc.id=vwreceived.contract_id) vwcontract_amount on kcc.id=vwcontract_amount.id\
                                        where \
                                            (contract_vat_vnd+contract_amount_vnd)!=total_vat_amount\
                                            or (contract_vat_vnd+contract_amount_vnd)!=(received_vnd+received_tax_vnd)));")
        
        #Status is UnCompleted, Oustanding is BC_Check
        cr.execute("Update kdvn_contract_client set status='uncompleted',outstanding='bc_check'\
                    where \
                        coalesce(status,'')<>'completed' and\
                        id not in \
                            (Select \
                                id \
                            from\
                                kdvn_contract_client kcc\
                            where    \
                                id not in (Select \
                                        distinct kpfc.contract_id\
                                       from \
                                        kdvn_payment_from_client kpfc\
                                       left join \
                                        kdvn_payment_term_line kptl on kpfc.id = kptl.id \
                                       where due_date<=current_date and state ='draft')\
                                and not (attached_approved_quotation=True or attached_contract_received=True))\
                        and id not in (Select \
                                kcc.id \
                            from \
                                kdvn_contract_client kcc \
                            where\
                                (attached_approved_quotation=True or attached_contract_received=True)\
                                and exists(Select id from kdvn_payment_from_client kpfc where kpfc.contract_id = kcc.id limit 1)\
                                and kcc.id not in (Select\
                                            contract_id \
                                        from \
                                            kdvn_payment_from_client\
                                        where coalesce(attached_progress_received,False)=False and coalesce(contract_id,0)>0)\
                                and kcc.id not in (Select \
                                            kcc.id\
                                        from \
                                            kdvn_contract_client kcc\
                                        left join\
                                            (Select\
                                                kpfc.contract_id,\
                                                sum(coalesce(kpvi.amount,0)) as total_vat_amount\
                                            from\
                                                kdvn_payment_from_client kpfc\
                                            left join\
                                                kdvn_payment_vat_invoice kpvi on kpfc.id = kpvi.payment_id\
                                            group by contract_id) vwpayment_vat on kcc.id = vwpayment_vat.contract_id\
                                        left join     \
                                            (Select \
                                                kcc.id,\
                                                ((coalesce(kcc.contract_amount_e,0)+coalesce(kcc.contract_amount_m,0))-coalesce(received_amount_as_contract_currency,0))*coalesce(fncompute_contract_currency_id(contract_currency),0)+coalesce(vwreceived.received_vnd,0) as contract_amount_vnd,\
                                                (coalesce(kcc.contract_vat,0)-coalesce(received_tax_amount_as_contract_currency,0))*coalesce(fncompute_contract_currency_id(contract_currency),0)+coalesce(vwreceived.received_tax_vnd,0) as contract_vat_vnd,\
                                                received_vnd,\
                                                received_tax_vnd\
                                            from \
                                                kdvn_contract_client kcc\
                                            left join     \
                                                (Select \
                                                    kpfc.contract_id,      \
                                                    sum(coalesce(kr.amount,0)*fncompute_contract_currency_id(kpfc.contract_currency_id,pc.id)) as received_amount_as_contract_currency,\
                                                    sum(coalesce(tax_amount,0)*fncompute_contract_currency_id(kpfc.contract_currency_id,pc.id)) as received_tax_amount_as_contract_currency,\
                                                    sum(coalesce(actual_amount,0)) as received_vnd,\
                                                    sum(coalesce(actual_tax_amount,0)) as received_tax_vnd\
                                                from \
                                                    kdvn_payment_from_client kpfc\
                                                left join \
                                                    kdvn_received kr on kpfc.id=payment_id\
                                                left join \
                                                    project_currency pc on kpfc.contract_id = pc.contract_id and default_curr=True\
                                                where \
                                                    kpfc.state<>'draft' and kpfc.state<>'cancel'\
                                                group by \
                                                    kpfc.contract_id) vwreceived on kcc.id=vwreceived.contract_id) vwcontract_amount on kcc.id=vwcontract_amount.id\
                                        where \
                                            (contract_vat_vnd+contract_amount_vnd)!=total_vat_amount\
                                            or (contract_vat_vnd+contract_amount_vnd)!=(received_vnd+received_tax_vnd)))")
        
        #Status is UnCompleted, Oustanding is PM_Check
        cr.execute("Update kdvn_contract_client set status='uncompleted',outstanding='pm_check' \
                    where \
                        coalesce(status,'')<>'completed' and\
                        id in \
                            (Select \
                                id \
                            from\
                                kdvn_contract_client kcc\
                            where    \
                                id not in (Select \
                                        distinct kpfc.contract_id \
                                       from \
                                        kdvn_payment_from_client kpfc\
                                       left join \
                                        kdvn_payment_term_line kptl on kpfc.id = kptl.id \
                                       where due_date<=current_date and state ='draft')\
                                and not (attached_approved_quotation=True or attached_contract_received=True));")
        return {}

    states = {
        'init' : {
            'actions' : [_update_contract_staus],
            'result' : {'type':'state', 'state':'end'}
        }
    }
wizard_update_contract_status('wizard_update_contract_status')